﻿Option Strict On

Imports Microsoft.VisualBasic
Imports System.Data.OleDb



Public Class CalendarDAO
    Dim connStr As String = ConfigurationManager.ConnectionStrings("ungvarberg").ConnectionString

    'Hämtar Alla Events
    Public Function getAllEvents() As List(Of Calendar)
        Dim sql As String = "SELECT AppointmentID, DateEvent, Header, Texten FROM tblCalendar"

        Dim connection As New OleDbConnection(connStr)
        Dim command As New OleDbCommand(sql, connection)

        Dim list As New List(Of Calendar)
        Dim Calendar As New Calendar

        Try
            connection.Open()
            Dim reader As OleDbDataReader = command.ExecuteReader

            While reader.Read()
                Calendar = New Calendar

                Calendar.AppointmentID = CType(reader("AppointmentID"), Integer)
                Calendar.DateEvent = CType(reader("DateEvent"), Date)
                Calendar.Header = reader("Header").ToString
                Calendar.Texten = reader("Texten").ToString

                list.Add(Calendar)
            End While

        Catch ex As Exception
            Throw ex
        Finally
            connection.Close()
            connection.Dispose()
        End Try

        Return list
    End Function

    'Hämta ett event
    Public Function getOneEvent(ByVal id As Integer) As Calendar
        Dim sql As String = "SELECT AppointmentID, DateEvent, Header, Texten FROM tblCalendar WHERE AppointmentID = @id"

        Dim connection As New OleDbConnection(connStr)
        Dim command As New OleDbCommand(sql, connection)

        command.Parameters.AddWithValue("@id", id)

        Dim Calendar As New Calendar

        Try
            connection.Open()
            Dim reader As OleDbDataReader = command.ExecuteReader

            If reader.Read() Then
                Calendar = New Calendar

                Calendar.AppointmentID = CType(reader("AppointmentID"), Integer)
                Calendar.DateEvent = CType(reader("DateEvent"), Date)
                Calendar.Header = reader("Header").ToString
                Calendar.Texten = reader("Texten").ToString
            End If

        Catch ex As Exception
            Throw ex
        Finally
            connection.Close()
            connection.Dispose()
        End Try

        Return Calendar
    End Function

    'Hämta alla events för EN dag
    Public Function GetAllEventFromOneDay(ByVal id As Integer) As List(Of Calendar)
        Dim sql As String = "SELECT AppointmentID, DateEvent, Header, Texten FROM tblCalendar WHERE dateevent = 2011-05-15"

        Dim list As New List(Of Calendar)

        Dim connection As New OleDbConnection(connStr)
        Dim command As New OleDbCommand(sql, connection)

        command.Parameters.AddWithValue("?", id)

        Dim Calendar As New Calendar

        Try
            connection.Open()
            Dim reader As OleDbDataReader = command.ExecuteReader

            While reader.Read()
                Calendar = New Calendar

                Calendar.AppointmentID = CType(reader("AppointmentID"), Integer)
                Calendar.DateEvent = CType(reader("DateEvent"), Date)
                Calendar.Header = reader("Header").ToString
                Calendar.Texten = reader("Texten").ToString

                list.Add(Calendar)
            End While

        Catch ex As Exception
            Throw ex
        Finally
            connection.Close()
            connection.Dispose()
        End Try

        Return list
    End Function
End Class
